﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;


namespace auxiliary_proj
{
    /// <summary>
    /// BJ's Code
    /// </summary>
    public partial class UCReport : UserControl
    {
        private static MySqlConnection DBCon;
        private static MySqlCommand comm;
        private static MySqlDataReader reader;

        List<string> unit_list = new List<string>();
        //Default constructor where both queries(getting all the units and display them in a combobox and also getting all the vendors and displaying tho
        // those in a combobox as well) get invoked
        public UCReport()
        {
            InitializeComponent();
            //double click on the sales order reports dgv gets instantiated
            this.SalesOrderDataGridView.CellContentDoubleClick += new DataGridViewCellEventHandler(SalesOrderDataGridView_DoubleCellContentClick);
            //double click on the purchase order report dgv gets instantiated
            this.POReportDataGridView.CellContentDoubleClick += new DataGridViewCellEventHandler(POReportDataGridView_DoubleCellContentClick);
            SalesOrderDataGridView.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige;
            POReportDataGridView.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige;

            lb_from.Visible = false;
            lbTo.Visible = false;
            salesdateTimeFrom.Visible = false;
            salesdateTimeTo.Visible = false;

            lbl_from.Visible = false;
            lbl_to.Visible = false;
            POdateTimeFrom.Visible = false;
            POdateTimeTo.Visible = false;

            cbSalesReports.SelectedIndex = 0;
            cbPOReports.SelectedIndex = 0;
            DBCon = new MySqlConnection(Program.getConnection());
            qGetUnits();
            qGetVendors();

        }

        public void setTab(int tab)
        {
            tabControl.SelectedIndex = tab;
        }

        private void calculatorToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("calc");
        }

        private void toolStripMenuItem8_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("calc");
        }

        // query all the vendors in the database and add them to a combobox
        public void qGetVendors()
        {
            cbVendor.Items.Clear();
            cbVendor.Items.Add("");
            string query = "SELECT name, vendor_id FROM vendor order by name";
            try
            {
                comm = new MySqlCommand(query, DBCon);
                DBCon.Open();
                reader = comm.ExecuteReader();
                {
                    while (reader.Read())
                    {
                        //adds the user to the combobox 
                        cbVendor.Items.Add(reader["name"].ToString());
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            DBCon.Close();
        }

        // query all the units in the database and add them to a combobox
        public void qGetUnits()
        {
            cbUnits.Items.Clear();
            cbUnits.Items.Add("");
            string query = "SELECT name, unit_id FROM unit order by name";
            try
            {
                comm = new MySqlCommand(query, DBCon);
                DBCon.Open();
                reader = comm.ExecuteReader();
                {
                    while (reader.Read())
                    {
                        //adds the user to the combobox 
                        cbUnits.Items.Add(reader["name"].ToString());
                        unit_list.Add(reader["unit_id"].ToString());
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            DBCon.Close();
        }
        //query all the sales orders that have been shipped and add them to the datagridview
        private void btnGetSales_Click(object sender, EventArgs e)
        {
            string query = "";
            string unit = "";
            if (cbUnits.Text.Trim().Length > 0)
            {
                SalesOrderDataGridView.Columns[0].Visible = false;
                lblUnitname.Text = cbUnits.SelectedItem.ToString();
                lblUnitname.Visible = true;
                unit = " AND u.name LIKE '%" + cbUnits.Text.Trim() + "%' ";
            }
            else
            {
                SalesOrderDataGridView.Columns[0].Visible = true;
                lblUnitname.Visible = false;
            }
            if (cbSalesReports.SelectedIndex == 0)
            {
                query = "SELECT u.name AS unitname, so.order_id AS order_num, DATE_FORMAT(so.timestamp, '%M %e, %Y, %l:%i%p') as Date, SUM(io.quantity*iq.price) AS total, so.approvedby AS approval FROM unit AS u, s_order AS so, item_order AS io, item_quantity AS iq WHERE so.status='3' AND u.unit_id = so.unit_id AND so.order_id = io.order_id AND iq.item_id= io.item_id" + unit + " GROUP BY io.order_id ORDER BY so.timestamp DESC ";
            }
            else if (cbSalesReports.SelectedIndex == 1)
            {
                query = "SELECT u.name AS unitname, so.order_id AS order_num, DATE_FORMAT(so.timestamp, '%M %e, %Y, %l:%i%p') as Date, SUM(io.quantity*iq.price) AS total, so.approvedby AS approval FROM unit AS u, s_order AS so, item_order AS io, item_quantity AS iq WHERE so.status='3' AND u.unit_id = so.unit_id AND so.order_id = io.order_id AND iq.item_id= io.item_id" + unit + " AND YEARWEEK(so.timestamp) = YEARWEEK(CURRENT_DATE) GROUP BY io.order_id ORDER BY so.timestamp DESC";
            }
            else if (cbSalesReports.SelectedIndex == 2)
            {
                query = "SELECT u.name AS unitname, so.order_id AS order_num, DATE_FORMAT(so.timestamp, '%M %e, %Y, %l:%i%p') as Date, SUM(io.quantity*iq.price) AS total, so.approvedby AS approval FROM unit AS u, s_order AS so, item_order AS io, item_quantity AS iq WHERE so.status='3' AND u.unit_id = so.unit_id AND so.order_id = io.order_id AND iq.item_id= io.item_id" + unit + " AND so.timestamp>= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY io.order_id ORDER BY so.timestamp DESC";
            }
            else if (cbSalesReports.SelectedIndex == 3)
            {
                query = "SELECT u.name AS unitname, so.order_id AS order_num, DATE_FORMAT(so.timestamp, '%M %e, %Y, %l:%i%p') as Date, SUM(io.quantity*iq.price) AS total, so.approvedby AS approval FROM unit AS u, s_order AS so, item_order AS io, item_quantity AS iq WHERE so.status='3' AND u.unit_id = so.unit_id AND so.order_id = io.order_id AND iq.item_id= io.item_id" + unit + " AND so.timestamp>= DATE_SUB(NOW(), INTERVAL 1 MONTH) GROUP BY io.order_id ORDER BY so.timestamp DESC";
            }
            else if (cbSalesReports.SelectedIndex == 4)
            {
                query = "SELECT u.name AS unitname, so.order_id AS order_num, DATE_FORMAT(so.timestamp, '%M %e, %Y, %l:%i%p') as Date, SUM(io.quantity*iq.price) AS total, so.approvedby AS approval FROM unit AS u, s_order AS so, item_order AS io, item_quantity AS iq WHERE so.status='3' AND u.unit_id = so.unit_id AND so.order_id = io.order_id AND iq.item_id= io.item_id" + unit + " AND so.timestamp>= DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY io.order_id ORDER BY so.timestamp DESC";
            }
            else if (cbSalesReports.SelectedIndex == 5)
            {
                query = "SELECT u.name AS unitname, so.order_id AS order_num, DATE_FORMAT(so.timestamp, '%M %e, %Y, %l:%i%p') as Date, SUM(io.quantity*iq.price) AS total, so.approvedby AS approval FROM unit AS u, s_order AS so, item_order AS io, item_quantity AS iq WHERE so.status='3' AND u.unit_id = so.unit_id AND so.order_id = io.order_id AND iq.item_id= io.item_id" + unit + " AND so.timestamp>='" + salesdateTimeFrom.Value.Date.ToString("yyyy/MM/dd") + "' AND so.timestamp<='" + salesdateTimeTo.Value.Date.ToString("yyyy/MM/dd 23:59:59") + "' GROUP BY io.order_id ORDER BY so.timestamp DESC";
            }
            try
            {
                SalesOrderDataGridView.DataSource = Query.select(query);
            }
            catch (MySqlException exc) { MessageBox.Show(exc.Message); }
        }

        //query all the purchase orders that are not outstanding and add them to the datagridview
        private void btnGetPO_Click(object sender, EventArgs e)
        {
            string query = "";
            string vendor = "", po_number = "";
            if (cbVendor.Text.Trim().Length > 0)
            {
                POReportDataGridView.Columns[0].Visible = false;
                lbl_vendorname.Text = cbVendor.SelectedItem.ToString();
                lbl_vendorname.Visible = true;
                vendor = " AND v.name LIKE '%" + cbVendor.Text.Trim() + "%' ";
            }
            else
            {
                POReportDataGridView.Columns[0].Visible = true;
                lbl_vendorname.Visible = false;
            }
            //add this to the query if the textbox is not empty
            if (!txtBoxSearchPO.Text.Equals(""))
            {
                po_number = " AND (p.po_number LIKE '%" + txtBoxSearchPO.Text + "%') ";
            }
            //query based off the date interval selected
            if (cbPOReports.SelectedIndex == 0)
            {
                query = "select v.name as vendor, p.po_number as PO, DATE_FORMAT(p.timestamp, '%M %e, %Y, %l:%i%p') as date, p.createdBy as created from vendor as v, purchase_order as p where v.vendor_id=p.vendor_id" + vendor + "" + po_number + " AND p.status='1' ORDER BY timestamp DESC";
            }
            else if (cbPOReports.SelectedIndex == 1)
            {
                query = "select v.name as vendor, p.po_number as PO, DATE_FORMAT(p.timestamp, '%M %e, %Y, %l:%i%p') as date, p.createdBy as created from vendor as v, purchase_order as p where v.vendor_id=p.vendor_id" + vendor + "" + po_number + " AND p.status='1' AND YEARWEEK(timestamp) = YEARWEEK(CURRENT_DATE) ORDER BY timestamp DESC";
            }
            else if (cbPOReports.SelectedIndex == 2)
            {
                query = "select v.name as vendor, p.po_number as PO, DATE_FORMAT(p.timestamp, '%M %e, %Y, %l:%i%p') as date, p.createdBy as created from vendor as v, purchase_order as p where v.vendor_id=p.vendor_id" + vendor + "" + po_number + " AND p.status='1' AND timestamp>= DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY timestamp DESC";
            }
            else if (cbPOReports.SelectedIndex == 3)
            {
                query = "select v.name as vendor, p.po_number as PO, DATE_FORMAT(p.timestamp, '%M %e, %Y, %l:%i%p') as date, p.createdBy as created from vendor as v, purchase_order as p where v.vendor_id=p.vendor_id" + vendor + "" + po_number + " AND p.status='1' AND timestamp>= DATE_SUB(NOW(), INTERVAL 1 MONTH) ORDER BY timestamp DESC";
            }
            else if (cbPOReports.SelectedIndex == 4)
            {
                query = "select v.name as vendor, p.po_number as PO, DATE_FORMAT(p.timestamp, '%M %e, %Y, %l:%i%p') as date, p.createdBy as created from vendor as v, purchase_order as p where v.vendor_id=p.vendor_id" + vendor + "" + po_number + " AND p.status='1' AND timestamp>= DATE_SUB(NOW(), INTERVAL 1 YEAR) ORDER BY timestamp DESC";
            }
            else if (cbPOReports.SelectedIndex == 5)
            {
                query = "select v.name as vendor, p.po_number as PO, DATE_FORMAT(p.timestamp, '%M %e, %Y, %l:%i%p') as date, p.createdBy as created from vendor as v, purchase_order as p where v.vendor_id=p.vendor_id" + vendor + "" + po_number + " AND p.status='1' AND timestamp>='" + POdateTimeFrom.Value.ToString("yyyy/MM/dd") + "' AND timestamp<='" + POdateTimeTo.Value.ToString("yyyy/MM/dd 23:59:59") + "'ORDER BY timestamp DESC";
            }
            try
            {
                POReportDataGridView.DataSource = Query.select(query);
            }
            catch (MySqlException exc) { MessageBox.Show(exc.Message); }
        }

        //invoke a new instance of the sales order summary form once a cell is double-clicked
        private void SalesOrderDataGridView_DoubleCellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            FormSalesOrderSummary fsos = new FormSalesOrderSummary((int)SalesOrderDataGridView.Rows[e.RowIndex].Cells["order_num"].Value, (String)SalesOrderDataGridView.Rows[e.RowIndex].Cells["unitname"].Value, (String)SalesOrderDataGridView.Rows[e.RowIndex].Cells["Date"].Value, (String)SalesOrderDataGridView.Rows[e.RowIndex].Cells["approval"].Value);
            fsos.StartPosition = FormStartPosition.CenterScreen;
            fsos.Show();
        }
        //invoke a new instance of the purchase order summary form once a cell is double-clicked
        private void POReportDataGridView_DoubleCellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            FormPOSummary fpos = new FormPOSummary((String)POReportDataGridView.Rows[e.RowIndex].Cells["PO"].Value, (String)POReportDataGridView.Rows[e.RowIndex].Cells["vendor"].Value, (String)POReportDataGridView.Rows[e.RowIndex].Cells["datetime"].Value, (String)POReportDataGridView.Rows[e.RowIndex].Cells["created"].Value);
            fpos.StartPosition = FormStartPosition.CenterParent;
            fpos.Show();
        }
        //show datepicker and all its labels once the interval option in the date combobox once the purchase order reports tab is clicked
        private void po_IndexChanged(object sender, EventArgs e)
        {
            if (cbPOReports.SelectedIndex == 5)
            {
                lbl_from.Visible = true;
                lbl_to.Visible = true;
                POdateTimeFrom.Visible = true;
                POdateTimeTo.Visible = true;
            }
            else
            {
                lbl_from.Visible = false;
                lbl_to.Visible = false;
                POdateTimeFrom.Visible = false;
                POdateTimeTo.Visible = false;
            }
        }

        //show datepicker and all its labels once the interval option in the date combobox in the sales tab is clicked
        private void sales_IndexChanged(object sender, EventArgs e)
        {
            if (cbSalesReports.SelectedIndex == 5)
            {
                lb_from.Visible = true;
                lbTo.Visible = true;
                salesdateTimeFrom.Visible = true;
                salesdateTimeTo.Visible = true;
            }
            else
            {
                lb_from.Visible = false;
                lbTo.Visible = false;
                salesdateTimeFrom.Visible = false;
                salesdateTimeTo.Visible = false;
            }
        }
    }
}
